package com.jlastu.znb.database;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;

import com.jlastu.znb.entity.Goods;
import com.jlastu.znb.entity.Photo;
import com.jlastu.znb.gwc.CartItem;

import java.util.ArrayList;
import java.util.List;

public class DBHelper extends SQLiteOpenHelper {

   private static final String TAG = "DBHelper";
   private static final String DATABASE_NAME = "znb.db";// 数据库名
   private static final int DATABASE_VERSION = 2;// 数据库版本号
   private static final String TABLE_NAME_GOODS = "goods"; //商品表
   private static final String TABLE_NAME_USER = "user"; //用户表
   private static final String TABLE_NAME_ORDER = "order2"; // 订单表 order是关键字
   private static final String TABLE_NAME_SETTINGS = "settings"; // 设置表
   private static final String TABLE_NAME_PHOTO = "photo";
  //购物车表
   public static class CartTable {
      public static final String NAME = "cart";
      public static final String _ID = "_id";
      public static final String GOODS_ID = "goods_id";
      public static final String QUANTITY = "quantity";
      public static final String USER_ID = "user_id";
   }
   //收藏表
   public static class scTable {
      public static final String NAME = "sc";
      public static final String _ID = "_id";
      public static final String GOODS_ID = "goods_id";
      public static final String QUANTITY = "quantity";
      public static final String USER_ID = "user_id";
   }
    public static class buyTable {
        public static final String NAME = "buy";
        public static final String _ID = "_id";
        public static final String GOODS_ID = "goods_id";
        public static final String QUANTITY = "quantity";
        public static final String USER_ID = "user_id";
    }
    public static class dfhTable {
        public static final String NAME = "dfh";
        public static final String _ID = "_id";
        public static final String GOODS_ID = "goods_id";
        public static final String QUANTITY = "quantity";
        public static final String USER_ID = "user_id";
    }
    public static class zjTable {
        public static final String NAME = "zj";
        public static final String _ID = "_id";
        public static final String GOODS_ID = "goods_id";
        public static final String QUANTITY = "quantity";
        public static final String USER_ID = "user_id";
    }
   public DBHelper(Context context) {
      super(context, DATABASE_NAME, null, DATABASE_VERSION);
   }

   //数据库创建时被调用
   @Override
   public void onCreate(SQLiteDatabase db) {
      initTable(db);
      initData(db);
      initCartTable(db);
      initscTable(db);
      initbuyTable(db);
      initdfhTable(db);
      initzjTable(db);
   }

   //数据库升级
   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      // 处理数据库升级逻辑
      switch (oldVersion) {
         case 1:
            // 创建 cart 表
            initCartTable(db);
            break;
          case 2:
              // 创建 sc 表
             initscTable(db);
              break;
          case 3:
              // 创建 buy 表
              initbuyTable(db);
              break;
          case 4:
              // 创建 buy 表
              initdfhTable(db);
              break;
          case 5:
              // 创建 buy 表
              initzjTable(db);
              break;
      }
   }

   /**
    * 初始化数据表
    *
    * @param db 数据库
    */
   private void initTable(SQLiteDatabase db) {
      // 创建商品表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_GOODS + " (" +
              "_id integer primary key autoincrement," +
              "goods_name text," +
              "goods_price double," +
              "goods_unit text," +
              "goods_message text)");
      // 创建用户表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_USER + " (" +
              "_id integer primary key autoincrement," +
              "user_name text," +
              "user_password text)");
      // 创建订单表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_ORDER + " (" +
              "_id integer primary key autoincrement," +
              "order_name text," +
              "order_number  bigint," +
              "user_id  integer," +
              "FOREIGN KEY (user_id) REFERENCES user(_id))");
      // 创建图片表
      db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_PHOTO + " (" +
              "_id integer primary key autoincrement," +
              "goods_id  integer," +
              "image BLOB," +
              "FOREIGN KEY (goods_id) REFERENCES goods(_id))");
   }

   /**
    * 初始化数据库内部模拟数据(仅在第一次安装时执行)
    *
    * @param db 数据库
    */
   private void initData(SQLiteDatabase db) {
//      db.execSQL("insert into " + TABLE_NAME_GOODS +
//              " (goods_name , goods_price, goods_unit, goods_message) " +
//              "values" +
//              "('仙豆', 1200, '颗',  '由住在卡林塔的卡林仙人（猫仙人）栽培，只要吃下一粒可以使人内外伤迅速自愈，并且十天不饿。')");
//

      db.execSQL("insert into " + TABLE_NAME_USER +
              " (user_name , user_password) " +
              "values" +
              "('1', '1')");
      db.execSQL("insert into " + TABLE_NAME_USER +
              " (user_name , user_password) " +
              "values" +
              "('2', '2')");
      db.execSQL("insert into " + TABLE_NAME_USER +
              " (user_name , user_password) " +
              "values" +
              "('3', '3')");
   }
   public static boolean checkUserExists(SQLiteDatabase db, String username) {
      String[] columns = { DBHelper.TABLE_NAME_USER + "._id" };
      String selection = DBHelper.TABLE_NAME_USER + ".user_name = ?";
      String[] selectionArgs = { username };
      Cursor cursor = db.query(
              DBHelper.TABLE_NAME_USER, columns, selection, selectionArgs, null, null, null
      );
      int cursorCount = cursor.getCount();
      cursor.close();
      return cursorCount > 0;
   }

   public static boolean loginUser(SQLiteDatabase db, String username, String password) {
      if (checkUserExists(db, username)) {
         // 这里需要添加逻辑来验证密码
         // 假设密码存储时未加密，实际应用中密码应该加密存储
         String[] columns = { DBHelper.TABLE_NAME_USER + ".user_password" };
         String selection = DBHelper.TABLE_NAME_USER + ".user_name = ?";
         String[] selectionArgs = { username };
         Cursor cursor = db.query(
                 DBHelper.TABLE_NAME_USER, columns, selection, selectionArgs, null, null, null
         );
         if (cursor.moveToFirst()) {
            String storedPassword = cursor.getString(0);
            cursor.close();
            return storedPassword.equals(password);
         }
         cursor.close();
      }
      return false;
   }

   public static void registerUser(SQLiteDatabase db, String username, String password) {
      ContentValues values = new ContentValues();
      values.put("user_name", username); // 移除 DBHelper.TABLE_NAME_USER + "_name"
      values.put("user_password", password);
      db.insert("user", null, values);
   }
   /**
    * 查询特定ID的商品
    *
    * @param goodsId 商品ID
    * @return 商品对象，如果未找到则返回null
    */
   @SuppressLint("Range")
   public Goods getGoodsById(int goodsId) {

      SQLiteDatabase db = this.getReadableDatabase();
      Goods goods = null;
      Cursor cursor = db.query(
              TABLE_NAME_GOODS,
              null,
              "_id = ?",
              new String[]{String.valueOf(goodsId)},
              null, null, null
      );


      if (cursor.moveToFirst()) {
         // 假设字段名和Goods类中的属性名一致
         goods = new Goods();
         goods.setId(cursor.getInt(cursor.getColumnIndex("_id")));
         goods.setName(cursor.getString(cursor.getColumnIndex("goods_name")));
         goods.setPrice(cursor.getDouble(cursor.getColumnIndex("goods_price")));
         goods.setUnit(cursor.getString(cursor.getColumnIndex("goods_unit")));
         goods.setMessage(cursor.getString(cursor.getColumnIndex("goods_message")));
      }
      cursor.close();
      List<Photo> photoList = new ArrayList<>();
      Cursor photoCursor = db.query(
              TABLE_NAME_PHOTO,
              null,
              "goods_id = ?",
              new String[]{String.valueOf(goodsId)},
              null, null, null
      );

      if (photoCursor.moveToFirst()) {
         do {
            Photo photo = new Photo();
            photo.setId(photoCursor.getInt(photoCursor.getColumnIndex("_id")));
            photo.setData(photoCursor.getBlob(photoCursor.getColumnIndex("image")));
            // 将 Photo 对象添加到列表
            photoList.add(photo);
         } while (photoCursor.moveToNext());
      }
      photoCursor.close();
      if (goods != null) {
         goods.setPhotoList(photoList);
      }
      db.close();
      return goods;
   }

   /**
    * 初始化购物车表
    */
   private void initCartTable(SQLiteDatabase db) {
      String createCartTableSQL = "CREATE TABLE IF NOT EXISTS " + CartTable.NAME + " ("
              + CartTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
              + CartTable.GOODS_ID + " INTEGER,"
              + CartTable.QUANTITY + " INTEGER,"
              + CartTable.USER_ID + " INTEGER,"
              + "FOREIGN KEY (" + CartTable.GOODS_ID + ") REFERENCES " + TABLE_NAME_GOODS + "(_id),"
              + "FOREIGN KEY (" + CartTable.USER_ID + ") REFERENCES " + TABLE_NAME_USER + "(_id)"
              + ")";
      db.execSQL(createCartTableSQL);
   }
   private void initscTable(SQLiteDatabase db) {
      String createscTableSQL = "CREATE TABLE IF NOT EXISTS " + scTable.NAME + " ("
              + scTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
              + scTable.GOODS_ID + " INTEGER,"
              + scTable.QUANTITY + " INTEGER,"
              + scTable.USER_ID + " INTEGER,"
              + "FOREIGN KEY (" + scTable.GOODS_ID + ") REFERENCES " + TABLE_NAME_GOODS + "(_id),"
              + "FOREIGN KEY (" + scTable.USER_ID + ") REFERENCES " + TABLE_NAME_USER + "(_id)"
              + ")";
      db.execSQL(createscTableSQL);
   }
    private void initbuyTable(SQLiteDatabase db) {
        String createbuyTableSQL = "CREATE TABLE IF NOT EXISTS " + buyTable.NAME + " ("
                + buyTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + buyTable.GOODS_ID + " INTEGER,"
                + buyTable.QUANTITY + " INTEGER,"
                + buyTable.USER_ID + " INTEGER,"
                + "FOREIGN KEY (" + buyTable.GOODS_ID + ") REFERENCES " + TABLE_NAME_GOODS + "(_id),"
                + "FOREIGN KEY (" + buyTable.USER_ID + ") REFERENCES " + TABLE_NAME_USER + "(_id)"
                + ")";
        db.execSQL(createbuyTableSQL);
    }
    private void initdfhTable(SQLiteDatabase db) {
        String createdfhTableSQL = "CREATE TABLE IF NOT EXISTS " + dfhTable.NAME + " ("
                + dfhTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + dfhTable.GOODS_ID + " INTEGER,"
                + dfhTable.QUANTITY + " INTEGER,"
                + dfhTable.USER_ID + " INTEGER,"
                + "FOREIGN KEY (" + dfhTable.GOODS_ID + ") REFERENCES " + TABLE_NAME_GOODS + "(_id),"
                + "FOREIGN KEY (" + dfhTable.USER_ID + ") REFERENCES " + TABLE_NAME_USER + "(_id)"
                + ")";
        db.execSQL(createdfhTableSQL);
    }
    private void initzjTable(SQLiteDatabase db) {
        String createzjTableSQL = "CREATE TABLE IF NOT EXISTS " + zjTable.NAME + " ("
                + zjTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + zjTable.GOODS_ID + " INTEGER,"
                + zjTable.QUANTITY + " INTEGER,"
                + zjTable.USER_ID + " INTEGER,"
                + "FOREIGN KEY (" + zjTable.GOODS_ID + ") REFERENCES " + TABLE_NAME_GOODS + "(_id),"
                + "FOREIGN KEY (" + zjTable.USER_ID + ") REFERENCES " + TABLE_NAME_USER + "(_id)"
                + ")";
        db.execSQL(createzjTableSQL);
    }

   /**
    * 从购物车中移除商品项
    * @param cartItemId 要移除的购物车项ID
    */
   public void removeFromCart(int cartItemId) {
      SQLiteDatabase db = this.getWritableDatabase();
      db.delete(CartTable.NAME, CartTable._ID + " = ?", new String[] { String.valueOf(cartItemId) });
   }
    public void removeFromCart1(int scItemId) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(scTable.NAME, scTable._ID + " = ?", new String[] { String.valueOf(scItemId) });
    }
    public void removeFromCart2(int buyItemId) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(buyTable.NAME, buyTable._ID + " = ?", new String[] { String.valueOf(buyItemId) });
    }
    public void removeFromCart3(int dfhItemId) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(dfhTable.NAME, dfhTable._ID + " = ?", new String[] { String.valueOf(dfhItemId) });
    }
}

